package cn.com.nes.site.handle;

import cn.com.nes.site.entity.iot.CascadeCell;
import cn.com.nes.site.entity.iot.NameCascade;
import cn.hutool.core.collection.CollectionUtil;
import com.alibaba.excel.annotation.write.style.ContentStyle;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFSheet;

import java.util.List;
import java.util.Optional;
import java.util.concurrent.atomic.AtomicInteger;
import java.util.stream.IntStream;

/**
 * 级联下拉框处理器
 *
 * @Author: ShenWei
 * @Date: 2024/05/10/15:14
 */
@Data
public class CascadeWriteHandler implements SheetWriteHandler {

    private final List<CascadeCell> cascadeCellList;

    private String dataSourceName;
    private static final Integer COLUMN = 16;
    public CascadeWriteHandler(List<CascadeCell> cascadeCellList) throws Exception {
        if (CollectionUtil.isEmpty(cascadeCellList)) {
            throw new Exception("cascadeCellList must not be empty");
        }
        this.cascadeCellList = cascadeCellList;
        this.dataSourceName = "dataSource";
    }

    /**
     * 前置处理
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    /**
     * 后置处理
     * @param writeWorkbookHolder
     * @param writeSheetHolder
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        int sort = 1;
        for (CascadeCell cascadeCell : cascadeCellList) {
            dataSourceName = dataSourceName + (sort++);
            // 获取工作簿
            Sheet sheet = writeSheetHolder.getSheet();
            sheet.setDefaultColumnWidth(16);
            Workbook book = writeWorkbookHolder.getWorkbook();
            // 创建一个专门用来存放下拉信息的隐藏sheet页
            // 因此不能在现实页之前创建，否则无法隐藏。
            Sheet hideSheet = book.createSheet(dataSourceName);
            book.setSheetHidden(book.getSheetIndex(hideSheet), true);
            // 将具体的数据写入到每一行中，每行的第一个单元格为父级区域的值，后面是子区域。
            List<NameCascade> nameCascadeList = cascadeCell.getNameCascadeList();
            if (CollectionUtil.isEmpty(nameCascadeList)) continue;

            // 第一层的选项设置到第一行
            IntStream.range(0, nameCascadeList.size()).forEach(i -> hideSheet.createRow(i).createCell(0).setCellValue(nameCascadeList.get(i).getName()));

            // 大类规则
            int firstRowIndex = cascadeCell.getRowIndex(); // 起始行
            int columnIndex = cascadeCell.getColumnIndex(); // 起始列
//            int lastRowIndex = firstRowIndex+nameCascadeList.size(); // 结束行
            int lastRowIndex = cascadeCell.getRowNum(); // 下拉框结束行

            // 开始设置（大类小类）下拉框
            DataValidationHelper dvHelper = sheet.getDataValidationHelper();
            CellRangeAddressList expRangeAddressList = new CellRangeAddressList(firstRowIndex, lastRowIndex, columnIndex, columnIndex);
            /*String bigEndCol = colIndex2Str(nameCascadeList.size());*/
            DataValidationConstraint bigFormula = dvHelper.createFormulaListConstraint("=" + dataSourceName + "!$A$1:$A$"+nameCascadeList.size());
            setValidation(sheet, dvHelper, bigFormula, expRangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");

            // 小类规则(各单元格按个设置)
            // "INDIRECT($A$" + 2 + ")" 表示规则数据会从名称管理器中获取key与单元格 A2 值相同的数据，如果A2是浙江省，那么此处就是浙江省下面的市
            // 为了让每个单元格的公式能动态适应，使用循环挨个给公式。
            // 循环几次，就有几个单元格生效，次数要和上面的大类影响行数一一对应，要不然最后几个没对上的单元格实现不了级联
            AtomicInteger rowId = new AtomicInteger(1);
            buildName(book, hideSheet, nameCascadeList, rowId);
            int maxLevel = getMaxLevel(nameCascadeList, 0);
            for (int num = 1; num < maxLevel; num++) {
                String start = colIndex2Str(columnIndex + num);
                String preStart = "$" + start + "$";
                for (int i = firstRowIndex; i <= lastRowIndex; i++) {
                    CellRangeAddressList rangeAddressList = new CellRangeAddressList(i, i, columnIndex + num, columnIndex + num);
                    DataValidationConstraint formula = dvHelper.createFormulaListConstraint("INDIRECT(" + preStart + (i + 1) + ")");
                    setValidation(sheet, dvHelper, formula, rangeAddressList, "提示", "你输入的值未在备选列表中，请下拉选择合适的值");
                }
            }
        }
        for(int i=0;i<COLUMN;i++){
            SXSSFSheet sheet = (SXSSFSheet)writeSheetHolder.getSheet();
            CellStyle cellStyle = writeWorkbookHolder.getCachedWorkbook().createCellStyle();
            //49为文本格式
            cellStyle.setDataFormat((short)49);
            sheet.setDefaultColumnStyle(i,cellStyle);
        }
    }

    // 添加名称管理器
    private void buildName(Workbook book, Sheet hideSheet, List<NameCascade> nameCascadeList, AtomicInteger rowId) {
        Optional.ofNullable(nameCascadeList).ifPresent(l -> l.forEach(nameCascade -> {
            List<NameCascade> childList = nameCascade.getNameCascadeList();
            if (childList != null && !childList.isEmpty()) {
                Row row = hideSheet.createRow(rowId.getAndIncrement());
                row.createCell(0).setCellValue(nameCascade.getName());
                IntStream.range(0, childList.size()).forEach(c ->
                        row.createCell(c + 1).setCellValue(childList.get(c).getName()));
                // 添加名称管理器
                String endCol = colIndex2Str(1 + childList.size());
                String range = "$B$" + rowId.get() + ":$" + endCol + "$" + rowId.get();

                Name name = book.createName();
                name.setNameName(nameCascade.getName());
                name.setRefersToFormula(dataSourceName + "!" + range);
                buildName(book, hideSheet, childList, rowId);
            }
        }));
    }

    // 递归查询最大层级数
    private int getMaxLevel(List<NameCascade> nameCascadeList, int preLevel) {
        int curLevel = preLevel + 1;
        int maxLevel = curLevel;
        for (NameCascade nameCascade : nameCascadeList) {
            List<NameCascade> childList = nameCascade.getNameCascadeList();
            if (childList != null && !childList.isEmpty()) {
                int level = getMaxLevel(childList, curLevel);
                maxLevel = Math.max(level, maxLevel);
            }
        }
        return maxLevel;
    }

    /**
     * 设置验证规则
     *
     * @param sheet       sheet对象
     * @param helper      验证助手
     * @param constraint  createExplicitListConstraint
     * @param addressList 验证位置对象
     * @param msgHead     错误提示头
     * @param msgContext  错误提示内容
     */
    private void setValidation(Sheet sheet, DataValidationHelper helper, DataValidationConstraint constraint, CellRangeAddressList addressList, String msgHead, String msgContext) {
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        dataValidation.setShowErrorBox(true);
        dataValidation.setSuppressDropDownArrow(true);
        dataValidation.createErrorBox(msgHead, msgContext);
        sheet.addValidationData(dataValidation);
    }

    public static String colIndex2Str(int column) {
        if (column <= 0) {
            return null;
        }
        String columnStr = "";
        column--;
        do {
            if (columnStr.length() > 0) {
                column--;
            }
            columnStr = ((char) (column % 26 + (int) 'A')) + columnStr;
            column = (int) ((column - column % 26) / 26);
        } while (column > 0);
        return columnStr;
    }
}
